Public Structure Comprens
    Public Id As String
    Public Sigla As String
    Public Descrizione As String
End Structure
Public Class cComuni
    Public Shared Function ElencoComuniComprensorio(ByVal idComprensorio As Integer) As System.Data.DataTable
        Dim cn As New Connessione
        Return cn.getDataTable("SELECT Codice, Descrizione FROM vistaComuniComprensorio WHERE IdComprensorio = " & idComprensorio & ";")
    End Function
    Public Shared Function ElencoComuniNoComprensorio(ByVal sProv As String) As System.Data.DataTable
        Dim cn As New Connessione
        If (IsNothing(sProv)) Then
            Return cn.getDataTable("SELECT Codice, Descrizione FROM vistaComuniComprensorio WHERE IdComprensorio IS NULL;")
        Else
            Return cn.getDataTable("SELECT Codice, Descrizione FROM vistaComuniComprensorio WHERE IdComprensorio IS NULL AND Provincia = '" & sProv & "';")
        End If
    End Function

    Public Shared Function caricaComboComprensori() As DataTable
        Dim conn As New Connessione
        Dim sql As String = "select null as codice,'' as descrizione union select id as codice,codice + ' - ' + descrizione as descrizione from TipoComprensorio"
        Dim dt As DataTable = conn.getDataTable(sql)
        Return dt
    End Function

    Public Shared Function caricaComboProvince() As DataTable
        Dim conn As New Connessione
        Dim sql As String = "select null as codice,'' as descrizione union select * from Province order by descrizione"
        Dim dt As DataTable = conn.getDataTable(sql)
        Return dt
    End Function
    Public Shared Sub AggiungiComuneAlComprensorio(ByVal codComune As Integer, ByVal codComeprensorio As Integer)
        Dim conn As New Connessione
        Dim sql As String = "INSERT INTO ComuniComprensori(IdComune, IdComprensorio) VALUES (" & codComune & "," & codComeprensorio & ");"
        conn.eseguiSql(sql)
    End Sub
    Public Shared Sub EliminaComuneDalComprensorio(ByVal codComune As Integer, ByVal codComeprensorio As Integer)
        Dim conn As New Connessione
        Dim sql As String = "DELETE FROM ComuniComprensori WHERE IdComune =" & codComune & " AND IdComprensorio = " & codComeprensorio & ";"
        conn.eseguiSql(sql)
    End Sub
    Public Shared Function getComprensorio(ByVal idComune As Integer) As Comprens
        Dim conn As New Connessione
        Dim sql As String = "SELECT TipoComprensorio.Id, TipoComprensorio.codice, TipoComprensorio.descrizione FROM ComuniComprensori LEFT OUTER JOIN TipoComprensorio ON ComuniComprensori.IdComprensorio = TipoComprensorio.id WHERE ComuniComprensori.IdComune = " & idComune
        Dim dt As DataTable = conn.getDataTable(sql)
        Dim ris As Comprens
        ris.Id = String.Empty
        ris.Sigla = String.Empty
        ris.Descrizione = String.Empty
        If dt.Rows.Count = 1 Then
            ris.Id = Utility.sNull(dt.Rows(0)(0))
            ris.Sigla = Utility.sNull(dt.Rows(0)(1))
            ris.Descrizione = Utility.sNull(dt.Rows(0)(2))
        End If
        Return ris
    End Function
End Class
